﻿Imports System.Data.OleDb
Module modDisplayShifts
    ''The listview is provided as a referenced argument
    Public Sub PopulateShiftsListFromAccess(ByRef lv As ListView)
        'Create a connection string to the MS Access database.
        Dim connModShifts As New OleDbConnection("Provider=Microsoft.jet.oledb.4.0;Data Source=|DataDirectory|\SkyCoffee.mdb")

        'Create a connection to the database
        'Dim conn As New System.Data.OleDb.OleDbConnection(connectionString)
        Dim com As System.Data.OleDb.OleDbCommand
        Dim reader As System.Data.OleDb.OleDbDataReader

        Try
            'Open the connection
            connModShifts.Open()
            'Create a new instance of the command and provide the SELECT query, and the opened connection
            com = New System.Data.OleDb.OleDbCommand("SELECT * FROM tblShifts", connModShifts)
            reader = com.ExecuteReader(CommandBehavior.CloseConnection)

            'Check to see if the SELECT query returned any rows
            If reader.HasRows Then
                'If so, perform a read for each row
                While reader.Read
                    'Declare a new ListViewItem, and provide the information
                    'to be shown in the very first column
                    Dim item As New ListViewItem(reader.Item("fldShiftID").ToString)

                    'Decare a new ListViewSubItem, and provide the information
                    'to be shown in the second (and so forth) column
                    Dim subItemShiftStartTime As New ListViewItem.ListViewSubItem()
                    subItemShiftStartTime.Text = reader.Item("fldShiftStartTime").ToString

                    Dim subItemShiftEndTime As New ListViewItem.ListViewSubItem()
                    subItemShiftEndTime.Text = reader.Item("fldShiftEndTime").ToString

                    Dim subItemShiftTotalTime As New ListViewItem.ListViewSubItem()
                    subItemShiftTotalTime.Text = reader.Item("fldShiftTotalTime").ToString

                    'Add the ListViewSubItem to the ListViewItem
                    item.SubItems.Add(subItemShiftStartTime)
                    item.SubItems.Add(subItemShiftEndTime)
                    item.SubItems.Add(subItemShiftTotalTime)

                    'Add the ListViewItem to the ListView
                    lv.Items.Add(item)

                    'Repeat until all rows have been read
                End While
            End If

            'Close the reader
            reader.Close()
        Catch ex As Exception
            'If something went sideways, make sure that you close the connection
            'before exiting the method.
            If connModShifts.State = ConnectionState.Open Then
                connModShifts.Close()
            End If
        End Try
    End Sub
End Module
